package ems.database;

import ems.lib.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class StaffDAO {

    private Connection conn = null; // DBConnection.connect()
    public Utility utility = new Utility();

    public StaffDAO() {
        this.conn = (DBConnection.getConnection() != null) ? DBConnection.getConnection() : DBConnection.connect();
    }

    /**
     * Get Staff object from username
     * @param username
     * @return Staff object and details if found. Null if not found
     */
    public Staff getStaff(String username) {
        Staff staff = null;
        try {

            PreparedStatement ps = conn.prepareStatement("SELECT * FROM STAFF WHERE USERNAME = ?");
            ps.setString(1, username);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String firstName = rs.getString("FIRST_NAME");
                String lastName = rs.getString("LAST_NAME");
                String status = rs.getString("STATUS");
                String job = rs.getString("JOB");
                String password = rs.getString("PASSWORD");                
                int id = rs.getInt("STAFF_ID");
                staff = new Staff(id, firstName, lastName, job, status, username, password);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

        return staff;
    }

    /**
     * Get Staff object from userid
     * @param userid
     * @return Staff object and details if found. Null if not found
     */
    public Staff getStaff(int userid) {
        Staff staff = null;
        try {

            PreparedStatement ps = conn.prepareStatement("SELECT * FROM STAFF WHERE STAFF_ID = ?");
            ps.setInt(1, userid);
            ResultSet rs = ps.executeQuery();
            if (rs.next()) {
                String firstName = rs.getString("FIRST_NAME");
                String lastName = rs.getString("LAST_NAME");
                String status = rs.getString("STATUS");
                String job = rs.getString("JOB");
                String password = rs.getString("PASSWORD");
                String username = rs.getString("USERNAME");                
                staff = new Staff(userid, firstName, lastName, job, status, username, password);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

        return staff;
    }

    /**
     * Get all staff with a specific filter
     * @param filter
     * @return list of staff
     */
    public List<Staff> getAllStaff(String filter) {        
        List<Staff> lstaff = new ArrayList<Staff>();
        Staff staff = null;
        try {
            String statusQ = " ";
            if (filter.equalsIgnoreCase("available") || filter.equalsIgnoreCase("av")) {
                statusQ = " WHERE status = 'AVAILABLE'";
            } else if (filter.equalsIgnoreCase("unavailable") || filter.equalsIgnoreCase("unav")) {
                statusQ = " WHERE status = 'UNAVAILABLE'";
            }
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM STAFF" + statusQ + " ORDER BY status");
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int userid = rs.getInt("STAFF_ID");
                String firstName = rs.getString("FIRST_NAME");
                String lastName = rs.getString("LAST_NAME");
                String status = rs.getString("STATUS");
                String job = rs.getString("JOB");
                String password = rs.getString("PASSWORD");
                String username = rs.getString("USERNAME");
                String type = rs.getString("TYPE");
                staff = new Staff(userid, firstName, lastName, job, status, username, password);
                lstaff.add(staff);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

        return lstaff;
    }

    /**
     * Get all Field Officer with a specific filter
     * @param filter
     * @return list of staff
     */
    public List<Staff> getAllFO(String filter) {
        List<Staff> lstaff = new ArrayList<Staff>();
        Staff staff = null;
        try {
            String statusQ = " ";
            if (filter.equalsIgnoreCase("available") || filter.equalsIgnoreCase("av")) {
                statusQ = " AND status = 'AVAILABLE'";
            } else if (filter.equalsIgnoreCase("unavailable") || filter.equalsIgnoreCase("unav")) {
                statusQ = " AND status = 'UNAVAILABLE'";
            }
            PreparedStatement ps = conn.prepareStatement("SELECT * FROM STAFF WHERE JOB='Field Officer'" + statusQ);
            
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                int userid = rs.getInt("STAFF_ID");
                String firstName = rs.getString("FIRST_NAME");
                String lastName = rs.getString("LAST_NAME");
                String status = rs.getString("STATUS");
                String job = rs.getString("JOB");
                String password = rs.getString("PASSWORD");
                String username = rs.getString("USERNAME");
                staff = new Staff(userid, firstName, lastName, job, status, username, password);
                lstaff.add(staff);
            }
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

        return lstaff;
    }

    /**
     * Adds a member of staff to the Database
     * @param staff
     * :<BR>A staff object containing the staff info
     * @return the ID for the staff member
     */
    public int createStaff(Staff staff) {
        int newID = 0;
        PreparedStatement psInsert = null;
        try {
            psInsert = conn.prepareStatement("INSERT INTO staff "
                    + "(Last_name, first_name, job, status,username,password) "
                    + "values (?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            psInsert.setString(1, staff.getLastName());
            psInsert.setString(2, staff.getFirstName());
            psInsert.setString(3, staff.getJob());
            psInsert.setString(4, staff.getStatus());
            psInsert.setString(5, staff.getUsername());
            psInsert.setString(6, staff.getPassword());

            psInsert.executeUpdate();
            conn.commit();

            ResultSet resultSet = psInsert.getGeneratedKeys();

            if (resultSet != null && resultSet.next()) {
                newID = resultSet.getInt(1);
            }


        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return newID;
    }

    /**
     * Adds a member of staff to the Database
     * NOTE: Status is set to 'Unavailable' as default
     * @param firstName
     * :<BR>First name of staff member
     * @param lastName
     * :<BR>Last name of staff member
     * @param job
     * :<BR>Job title of staff member
     * @param username
     * :<BR>Login username of staff member
     * @param password
     * :<BR>Login password of staff member
     * @return the Generated ID of the staff member
     */
    public int createStaff(String firstName, String lastName, String job, String username, String password) {
        int newID = 0;
        PreparedStatement psInsert = null;
        try {
            psInsert = conn.prepareStatement("INSERT INTO staff "
                    + "(Last_name, first_name, job, status,username,password) "
                    + "values (?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            psInsert.setString(1, lastName);
            psInsert.setString(2, firstName);
            psInsert.setString(3, job);
            psInsert.setString(4, "Unavailable");
            psInsert.setString(5, username);
            psInsert.setString(6, password);

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return newID;
    }

    /**
     * Adds a member of staff to the Database
     * NOTE: Status is set to 'Unavailable' as default,
     * Job is set to 'Unassigned' as default.
     * @param firstName
     * :<BR>First name of staff member
     * @param lastName
     * :<BR>Last name of staff member
     * @param username
     * :<BR>Login username of staff member
     * @param password
     * :<BR>Login password of staff member
     * @return the Generated ID of the staff member
     */
    public int createStaff(String firstName, String lastName, String username, String password) {
        int newID = 0;
        PreparedStatement psInsert = null;
        try {
            psInsert = conn.prepareStatement("INSERT INTO staff "
                    + "(Last_name, first_name, job, status,username,password) "
                    + "values (?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            psInsert.setString(1, lastName);
            psInsert.setString(2, firstName);
            psInsert.setString(3, "Unassigned");
            psInsert.setString(4, "Unavailable");
            psInsert.setString(5, username);
            psInsert.setString(6, password);

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }

        return newID;
    }

    /**
     * Adds a member of staff to the Database
     * NOTE: Status is set to 'Unavailable' as default,
     * Username and passowrds are set by default to the lastname plus the first letter of their first;
     * eg John Smith = SmithJ
     * @param firstName
     * :<BR>First name of staff member
     * @param lastName
     * :<BR>Last name of staff member
     * @param job
     * :<BR>Job title of staff member

     * @return the Generated ID of the staff member
     */
    public int createStaff(String firstName, String lastName, String job) {
        int newID = 0;
        PreparedStatement psInsert = null;
        try {
            psInsert = conn.prepareStatement("INSERT INTO staff "
                    + "(Last_name, first_name, job, status,username,password) "
                    + "values (?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            psInsert.setString(1, lastName);
            psInsert.setString(2, firstName);
            psInsert.setString(3, job);
            psInsert.setString(4, "Unavailable");
            psInsert.setString(5, (lastName + firstName.charAt(0)));
            psInsert.setString(6, (lastName + firstName.charAt(0)));

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return newID;
    }

    /**
     * Adds a member of staff to the Database
     * NOTE: Status is set to 'Unavailable' as default,
     * Job is set to 'Unassigned' by default,
     * Username and passwords are set by default to the last name plus the first letter of their first;
     * eg John Smith = SmithJ
     * @param firstName
     * :<BR>First name of staff member
     * @param lastName
     * :<BR>Last name of staff member
     * @return the Generated ID of the staff member
     */
    public int createStaff(String firstName, String lastName) {
        int newID = 0;
        PreparedStatement psInsert = null;
        try {
            psInsert = conn.prepareStatement("INSERT INTO staff "
                    + "(Last_name, first_name, job, status,username,password) "
                    + "values (?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);
            psInsert.setString(1, lastName);
            psInsert.setString(2, firstName);
            psInsert.setString(3, "Unassigned");
            psInsert.setString(4, "Unavailable");
            psInsert.setString(5, (lastName + firstName.charAt(0)));
            psInsert.setString(6, (lastName + firstName.charAt(0)));

        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return newID;
    }    

    public Object[][] getAll() {
        Object[][] staff = {};
        Statement s = null;
        ResultSet rs = null;
        try {
            s = conn.createStatement();
            rs = s.executeQuery("SELECT * FROM staff");
            staff = utility.resultSetToObjectArray(rs);
        } catch (SQLException sqle) {
            sqle.printStackTrace();
        }
        return staff;
    }
}
